﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Objects;
using Northwind.NET.Model;
using Northwind.NET.Settings;
using System.Data.Common;

namespace Northwind.NET.EDM.FE.WinForms.Controls
{
    public partial class CategoriesUserControl : BaseUserControl
    {
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            LoadCategories();
            LoadProducts();
        }
        public CategoriesUserControl()
        {
            InitializeComponent();
        }

        public override string FormCaption     { get { return Settings.Resources.CategoriesFormCaption; } }
        public override dynamic GetEntitySet(ObjectContext context)
        {
            return new Category();
        }

        public override BindingSource UserControlBindingSource { get { return categoryBindingSource; } }

         internal override void SetBindingSource()
        {
            // it has to be used here, if BaseUserControl's code is used then
            // picture binding fails
            categoryBindingSource.DataSource = GetEntitySet(null);
        }

        private void LoadCategories()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                DbDataAdapter adapter = Factory.CreateDataAdapter();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT ID, Name, Description FROM Categories";

                adapter.SelectCommand = command;

                DataSet dataset = new DataSet();
                dataset.Tables.Add("Categories");
                adapter.Fill(dataset.Tables["Categories"]);

                this.categoryBindingSource.DataSource = dataset.Tables["Categories"];

            }
        }
        void OnMove(object sender, System.EventArgs e)
        {
            string categoryName = this.txtName.Text;

            long categoryID = 0;
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT ID FROM Categories WHERE Name = '" + categoryName + "'";

                categoryID = (long)command.ExecuteScalar();

            }

            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                DbDataAdapter adapter = Factory.CreateDataAdapter();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT Name, QuantityPerUnit, UnitPrice, Discontinued " +
                                    "FROM Products WHERE Category.ID = " + categoryID;

                adapter.SelectCommand = command;

                DataSet dataset = new DataSet();
                dataset.Tables.Add("Products");
                adapter.Fill(dataset.Tables["Products"]);

                this.productDataGridView.DataSource = dataset.Tables["Products"];

            }
        }
        private void LoadProducts()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                DbDataAdapter adapter = Factory.CreateDataAdapter();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT Name, QuantityPerUnit, UnitPrice, Discontinued " + 
                                    "FROM Products WHERE Category.ID = 1";

                adapter.SelectCommand = command;

                DataSet dataset = new DataSet();
                dataset.Tables.Add("Products");
                adapter.Fill(dataset.Tables["Products"]);

                this.productDataGridView.DataSource = dataset.Tables["Products"];

            }
        }
    }
}
